import lombok.SneakyThrows;
import org.junit.jupiter.api.Test;
import tech.mhuang.pacebox.core.file.FileUtil;

import java.io.File;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.Set;
import java.util.concurrent.CopyOnWriteArraySet;

/**
 * @description:
 * @author: yf
 * @date: 2022-10-26 09:46:35
 */
public class BusinessDataTransTest2 {
    private final Connection connection;

    public BusinessDataTransTest2() throws SQLException {
        Driver driver = new com.mysql.cj.jdbc.Driver();
        Properties properties = new Properties();
        properties.setProperty("user", "develop");
        properties.setProperty("password", "zhzc123.");
        connection = driver.connect("jdbc:mysql://127.0.0.1:3306/asset-v3-dev", properties);
    }

    //种类配置
    @SneakyThrows
    @Test
    public void transInstitutionBatch() {
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(
                "SELECT ins_batch.*, " +
                        "pre_conf.max_principal_reduce_ratio, pre_conf.max_interest_reduce_ratio, pre_conf.max_penalty_reduce_ratio,pre_conf.max_periods,pre_conf.remark\n" +
                        "FROM bs_asset_institution_batch ins_batch\n" +
                        "LEFT JOIN bs_preferential_conf pre_conf on pre_conf.asset_inst_batch_id = ins_batch.id");
        Set<String> idSet = new CopyOnWriteArraySet<>();
        new File("d:\\4.0\\bs_asset_institution_batch.sql").createNewFile();
        while (resultSet.next()) {
            String id = resultSet.getString("id");
            if (!idSet.contains(id)) {
                idSet.add(id);
                StringBuilder sqlBuilder = new StringBuilder();
                //alias,result,detailId,detailCreateTime
                sqlBuilder.append("INSERT INTO bs_asset_institution_batch (id, asset_institution_batch_code, asset_institution_batch_name, description, case_type, asset_institution_id, verify, loan_verify, allowOuth, allowUpdate, allowImport, " +
                        "create_user, operate_user, create_time, operate_time, cooperation_method, del_flag, principal_max_scale, interest_max_scale, penalty_max_scale," +
                        " max_period, content) values(");
                genertorStringSql(resultSet, "id", sqlBuilder, true);
                genertorStringSql(resultSet, "asset_institution_batch_code", sqlBuilder, true);
                genertorStringSql(resultSet, "asset_institution_batch_name", sqlBuilder, true);
                genertorStringSql(resultSet, "description", sqlBuilder, true);
                genertorStringSql(resultSet, "case_type", sqlBuilder, true);
                genertorStringSql(resultSet, "asset_institution_id", sqlBuilder, true);
                genertorStringSql(resultSet, "verify", sqlBuilder, true);
                genertorStringSql(resultSet, "o_verify", sqlBuilder, true);
                genertorStringSql(resultSet, "allowOuth", sqlBuilder, true);
                genertorStringSql(resultSet, "allowUpdate", sqlBuilder, true);
                genertorStringSql(resultSet, "allowImport", sqlBuilder, true);
                genertorStringSql(resultSet, "create_user", sqlBuilder, true);
                genertorStringSql(resultSet, "operate_user", sqlBuilder, true);
                sqlBuilder.append("'" + resultSet.getTimestamp("create_time") + "',");
                sqlBuilder.append("'" + resultSet.getTimestamp("operate_time") + "',");
                genertorStringSql(resultSet, "cooperation_method", sqlBuilder, true);
                sqlBuilder.append("'0',");
                genertorStringSql(resultSet, "max_principal_reduce_ratio", sqlBuilder, true);
                genertorStringSql(resultSet, "max_interest_reduce_ratio", sqlBuilder, true);
                genertorStringSql(resultSet, "max_penalty_reduce_ratio", sqlBuilder, true);
                genertorIntergeSql(resultSet, "max_periods", sqlBuilder, true);
                genertorStringSql(resultSet, "remark", sqlBuilder, false);
                sqlBuilder.append(");\n");
                FileUtil.writeStringToFile(new File("d:\\4.0\\bs_asset_institution_batch.sql"), sqlBuilder.toString(), true);
            }
        }
        resultSet.close();
        statement.close();
    }

    //;
    //案件处置明细表
    @SneakyThrows
    @Test
    public void transDisposalFile() {
        Statement statement = connection.createStatement();

        //bs_case_disposal_file表
        ResultSet resultSet = statement.executeQuery("SELECT id, case_id, current_state, original_name, replace_name, file_path, create_user, create_time, operate_user, operate_time, action_key, file_type, upload_way FROM bs_case_disposal_file");
        Set<String> idSet = new CopyOnWriteArraySet<>();
        new File("d:\\4.0\\bs_case_disposal_file.sql").createNewFile();
        while (resultSet.next()) {
            String id = resultSet.getString("id");
            if (!idSet.contains(id)) {
                idSet.add(id);
                StringBuilder sqlBuilder = new StringBuilder();
                //alias,result,detailId,detailCreateTime
                sqlBuilder.append("INSERT INTO bs_case_disposal_file (id, case_id, current_state, original_name, replace_name, file_path, create_user, create_time, operate_user, operate_time, file_type, upload_way) values(");
                genertorStringSql(resultSet, "id", sqlBuilder, true);
                genertorStringSql(resultSet, "case_id", sqlBuilder, true);
                genertorStringSql(resultSet, "current_state", sqlBuilder, true);
                genertorStringSql(resultSet, "original_name", sqlBuilder, true);
                genertorStringSql(resultSet, "replace_name", sqlBuilder, true);
                genertorStringSql(resultSet, "file_path", sqlBuilder, true);
                genertorStringSql(resultSet, "create_user", sqlBuilder, true);
                genertorStringSql(resultSet, "create_time", sqlBuilder, true);
                genertorStringSql(resultSet, "operate_user", sqlBuilder, true);
                genertorStringSql(resultSet, "operate_time", sqlBuilder, true);
                genertorStringSql(resultSet, "file_type", sqlBuilder, true);
                genertorStringSql(resultSet, "upload_way", sqlBuilder, false);
                sqlBuilder.append(");\n");
                FileUtil.writeStringToFile(new File("d:\\4.0\\bs_case_disposal_file.sql"), sqlBuilder.toString(), true);
            }
        }


        resultSet = statement.executeQuery("SELECT id, file_id, alias, `result`, create_time FROM bs_case_disposal_file_result");
        new File("d:\\4.0\\bs_case_disposal_file_result.sql").createNewFile();
        while (resultSet.next()) {
            String id = resultSet.getString("id");
            if (!idSet.contains(id)) {
                idSet.add(id);
                StringBuilder sqlBuilder = new StringBuilder();
                //alias,result,detailId,detailCreateTime
                sqlBuilder.append("INSERT INTO bs_case_disposal_file_result (id, file_id, alias, `result`, create_time) values(");
                genertorStringSql(resultSet, "id", sqlBuilder, true);
                genertorStringSql(resultSet, "file_id", sqlBuilder, true);
                genertorStringSql(resultSet, "alias", sqlBuilder, true);
                genertorStringSql(resultSet, "result", sqlBuilder, true);
                genertorStringSql(resultSet, "create_time", sqlBuilder, false);
                sqlBuilder.append(");\n");
                FileUtil.writeStringToFile(new File("d:\\4.0\\bs_case_disposal_file_result.sql"), sqlBuilder.toString(), true);
            }
        }
        resultSet.close();
        statement.close();
    }

    private void genertorStringSql(ResultSet rs, String param, StringBuilder sqlBuilder, boolean checked) throws SQLException {
        if (rs.getString(param) != null) {
            sqlBuilder.append("'" + rs.getString(param) + "'");
        } else {
            sqlBuilder.append(rs.getString(param));
        }
        if (checked) {
            sqlBuilder.append(",");
        }
    }

    //处理String 转int
    private void genertorIntergeSql(ResultSet rs, String param, StringBuilder sqlBuilder, boolean checked) throws SQLException {
        if (rs.getString(param) != null) {
            sqlBuilder.append("" + Integer.valueOf(rs.getString(param)) + "");
        } else {
            sqlBuilder.append(rs.getString(param));
        }
        if (checked) {
            sqlBuilder.append(",");
        }
    }
}
